/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAL;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import xmlobject.DTO.Products.Product;
import xmlobject.DTO.Products.Products;

/**
 *
 * @author minhsong
 */
public class ProductDAL extends Dataconection {

    private Product ReadProductFromDS(ResultSet rs) {
        Product result = new Product();
        if (rs != null) {
            try {
                result.setProductID(rs.getLong("ProductID"));
                result.setProductName(rs.getString("ProductName"));
                result.setProductImage(rs.getString("ProductImage"));
                result.setProductDesc(rs.getString("ProductDesc"));
                result.setWeight(rs.getLong("Weight"));
                result.setHeight(rs.getLong("Height"));
                result.setLong(rs.getLong("Long"));
                result.setWide(rs.getInt("Wide"));
                result.setCreateBy(rs.getString("CreateBy"));
                result.setCreateDate(rs.getDate("CreateDate"));
                result.setUpdateBy(rs.getString("UpdateBy"));
                result.setUpdateDate(rs.getDate("UpdateDate"));
                result.setPrice(rs.getDouble("Price"));
                result.setStock(rs.getLong("Stock"));
                result.setUnit(rs.getString("Unit"));
            } catch (SQLException ex) {
                Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
        return result;
    }

    public Product getProductById(int id) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spproductGet(?) }");
            proc_stmt.setLong("ProductID", id);
            rs = proc_stmt.executeQuery();
            if (rs.next()) {
                return ReadProductFromDS(rs);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public Products getAllProduct() {
        Products result = new Products();
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spproductGetList() }");
            rs = proc_stmt.executeQuery();
            while (rs.next()) {
                result.getProduct().add(ReadProductFromDS(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public Products getProductsByCategoryId(int id) {
        Products result = new Products();
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spproductGetByCategoryId(?) }");
            proc_stmt.setLong("CatogeryID", id);
            rs = proc_stmt.executeQuery();
            while (rs.next()) {
                result.getProduct().add(ReadProductFromDS(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public Product Create(Product value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spproductInsert(?,?,?,?,?,?,?,?,?,?,?,?,?) }");
            proc_stmt.registerOutParameter("ProductID", java.sql.Types.INTEGER);
            proc_stmt.setString("ProductName", value.getProductName());
            proc_stmt.setString("ProductImage", value.getProductImage());
            proc_stmt.setString("ProductDesc", value.getProductDesc());
            proc_stmt.setLong("Weight", value.getWeight());
            proc_stmt.setLong("Height", value.getHeight());
            proc_stmt.setLong("Long", value.getLong());
            proc_stmt.setInt("Wide", value.getWide());
            proc_stmt.setString("CreateBy", value.getCreateBy());
            proc_stmt.setString("UpdateBy", value.getUpdateBy());
            proc_stmt.setDouble("Price", value.getPrice());
            proc_stmt.setLong("Stock", value.getStock());
            proc_stmt.setString("Unit", value.getUnit());
            proc_stmt.execute();
            value.setProductID(proc_stmt.getLong("ProductID"));
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return value;
    }

    public Product Edit(Product value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spproductUpdate(?,?,?,?,?,?,?,?,?,?,?,?) }");
            proc_stmt.setLong("ProductID", value.getProductID());
            proc_stmt.setString("ProductName", value.getProductName());
            proc_stmt.setString("ProductImage", value.getProductImage());
            proc_stmt.setString("ProductDesc", value.getProductDesc());
            proc_stmt.setLong("Weight", value.getWeight());
            proc_stmt.setLong("Height", value.getHeight());
            proc_stmt.setLong("Long", value.getLong());
            proc_stmt.setInt("Wide", value.getWide());
            proc_stmt.setString("UpdateBy", value.getUpdateBy());
            proc_stmt.setDouble("Price", value.getPrice());
            proc_stmt.setLong("Stock", value.getStock());
            proc_stmt.setString("Unit", value.getUnit());
            proc_stmt.execute();
            return value;
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public boolean Delete(int value) {
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spproductDelete(?) }");
            proc_stmt.setLong("ProductID", value);
            return proc_stmt.execute();
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public Products Search(String name) {
        Products result = new Products();
        try {
            proc_stmt = DAOConnection.prepareCall("{ call spproductSearch(?) }");
            proc_stmt.setString("ProductName", name);
            rs = proc_stmt.executeQuery();
            while (rs.next()) {
                result.getProduct().add(ReadProductFromDS(rs));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ProductDAL.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
}
